package com.nstechs.commerce.transaction.productitem;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import com.nstechs.commerce.common.util.BuildUtil;
import com.nstechs.commerce.transaction.productitem.domain.SimplePoItem;


public class InventoryDataTranfer {
	private static Logger logger = LoggerFactory.getLogger("inventory");
	private ConnectionUtils targetConnUtils = null;
	private ConnectionUtils sourceConnUtils = null;
	private static String CHECK_FOREIGN_KEY= "SET FOREIGN_KEY_CHECKS=0";
	private static String INSERT_INVENTORY_DETAIL = "insert into warehouses.inventorydetail(create_by,created_date,quantity,inventory_id,sku,product_unit_id) select 'System', i.created_date, pu.received_quantity, i.id, pu.sku, pu.id from warehouses.productunit pu inner join warehouses.inventory i on pu.sku = i.sku";
	private static String INSERT_VENDOR = "insert into supplychains.vendor(id, address1, address2, city_name, country_name, created_by, created_date, email, first_name, last_name, lead_time, minimun_order_size, minimun_order_units, name,phone, state,status, updated_by, updated_date, zip_code, batch_id) values(1,'','','hs','1', 'System', now() ,'isket.job@busses.com', 'hs','hs', null , null, null, 'hs', '6579176103' , 'hs' , 'A', null, null, '', null)";
	private static String INSERT_POBATCH = "insert into supplychains.purchasebatch(id, created_by, created_date, failed_num, success_num) values(?,'System', now(), 0, 0)";
	private static String INSERTPO = "INSERT INTO supplychains.purchaseorder (create_by, created_date, purchase_order_no, STATUS, batch_id, vendor_id ) values('System', now(), ?, 'Y', 1, ?)";
	private static String INSERT_POITEM = "insert into supplychains.purchaseorderitem(can_receive_quantity, cost, currency, created_by, created_date, quantity, status, vendor_sku, sku, purchase_order_id) values(?, ?, ?, 'System', now(), ?, 'Y',? ,?, ?)";
	private static String INVENTORY_CHANGE_HISTORY = "insert into warehouses.inventorychangehistory(create_by,created_date,change_quantity,change_reason,operation_type,inventory_id,sku) select 'System',i.created_date ,pu.received_quantity,'Product receiving','P',i.id,pu.sku from warehouses.productunit pu inner join warehouses.inventory i on pu.sku = i.sku";
	private static String GET_INVENTORY_INFO = "SELECT skid.sku, skid.quantity, skid.on_picking_quantity, vpi.cost, vpi.currency, vpi.vendor_id, vpi.vendor_sku, skid.bin_id, skid. STATUS, skid.is_processed, skid.skid_code FROM warehouses.skid AS skid LEFT JOIN supplychains.vendor_product_item AS vpi ON skid.sku = vpi.sku WHERE skid. STATUS = 'A' AND vendor_id IS NOT NULL";
	private static String V_INVENTORY = "INSERT INTO products.virtualinventory ( available_quantity, onhand_quantity, ordered_quantity, prepared_inventory_quantity, prepared_orderd_quantity, wms_code, product_item_rack_id ) SELECT iv.onhand_quantity, iv.onhand_quantity, iv.ordered_quantity, iv.prepare_inventory_quantity, iv.prepare_ordered_quantity, iv.wms_code, pr.id FROM products.productitemrack pr INNER JOIN warehouses.inventory iv ON iv.sku = pr.sku";
	private static String II = "call warehouses.update_inventory();";
	
	
	public static void main(String[] args) throws SQLException {
		InventoryDataTranfer tranfer = new InventoryDataTranfer();
		tranfer.initconn ();
		tranfer.init();
		tranfer.saveHistory();
	}
	
	public void inventoryTransfer() throws SQLException {
		InventoryDataTranfer tranfer = new InventoryDataTranfer();
		tranfer.initconn ();
		tranfer.init();
		tranfer.saveHistory();
	}
	// init
	public void init() throws SQLException {
		ResultSet rs = sourceConnUtils.executeQuery(GET_INVENTORY_INFO);
		Map<String, List<SimplePoItem>> podatas = new HashMap<String, List<SimplePoItem>>();
		targetConnUtils.execute(INSERT_POBATCH);
		int key = 0;
		String batchId = "";
		targetConnUtils.execute(CHECK_FOREIGN_KEY);
		while (rs.next()) {
			String vendor_id = rs.getString("vendor_id");
			if (vendor_id != null && !podatas.containsKey(vendor_id)) {
				batchId = BuildUtil.randomNumber(14);
				targetConnUtils.execute(INSERT_POBATCH, batchId);
				targetConnUtils.execute("ALTER TABLE supplychains.purchaseorder auto_increment=10000");
				key = targetConnUtils.excuteQueryAndGetKey("INSERT INTO supplychains.purchaseorder(create_by, created_date, purchase_order_no, STATUS, batch_id, vendor_id ) values('System', now(), " + BuildUtil.randomNumber(14) +",'Y'," + batchId + " , " + Integer.valueOf(vendor_id) + ");");
				List<SimplePoItem> poitems = new ArrayList<SimplePoItem>();
				logger.info("purchaseorder id " + key);
				SimplePoItem poi = new SimplePoItem(rs.getDouble("quantity"), rs.getDouble("quantity"), rs.getDouble("cost"), rs.getString("sku"), key);
				poi.setCurrency(rs.getString("currency"));
				poi.setBinId(rs.getInt("bin_id"));
				poi.setSkidTagNumber(rs.getString("skid_code"));
				poi.setVenderSku(rs.getString("vendor_sku"));
				poitems.add(poi);
				podatas.put(vendor_id, poitems);
			} else {
				SimplePoItem poi = new SimplePoItem(rs.getDouble("quantity"), rs.getDouble("quantity"), rs.getDouble("cost"), rs.getString("sku"), key);
				poi.setCurrency(rs.getString("currency"));
				poi.setBinId(rs.getInt("bin_id"));
				poi.setSkidTagNumber(rs.getString("skid_code"));
				poi.setVenderSku(rs.getString("vendor_sku"));
				List<SimplePoItem> poitems = podatas.get(vendor_id);
				poitems.add(poi);
				podatas.put(vendor_id, poitems);
			}
		}
		
		int sucessNum = 0;
		int failedNum = 0;
		Map<String, SimplePoItem> soip =  new HashMap<String, SimplePoItem>();
		
		for (String poid : podatas.keySet()) {
			for (SimplePoItem simplePoItem : podatas.get(poid)) {
				if (simplePoItem.getSkidTagNumber() == null) {
					failedNum ++;
					logger.info("insert falied purchaseorderitem(can_receive_quantity {}, cost {}, created_by {}, created_date {}, quantity {}, status {}, vendor_sku {}, sku {}, purchase_order_id {}  error:skid_code is null", new Object[]{simplePoItem.getCanReceiveQuantity(),  simplePoItem.getCost(), "System", simplePoItem.getQuantity(), "Y", "VENDOR#HS", simplePoItem.getSku(), simplePoItem.getPurchaseOrderId()});
				} else {
					sucessNum ++;
					String sku = simplePoItem.getSku();
					if (sku != null && !soip.containsKey(sku)) {
						soip.put(sku, simplePoItem);
					} else {
						SimplePoItem poi = soip.get(sku);
						poi.setCanReceiveQuantity(simplePoItem.getCanReceiveQuantity() + poi.getCanReceiveQuantity());
						poi.setQuantity(simplePoItem.getQuantity() + poi.getQuantity());
						soip.put(sku, poi);
					}
				}
			}
		}
		
		for (String sku : soip.keySet()) {
			SimplePoItem simplePoItem = soip.get(sku);
			targetConnUtils.execute(INSERT_POITEM, simplePoItem.getCanReceiveQuantity(),  simplePoItem.getCost(), simplePoItem.getCurrency(), simplePoItem.getQuantity(), simplePoItem.getVenderSku(), simplePoItem.getSku(), simplePoItem.getPurchaseOrderId());
			logger.info("supplychains.purchaseorderitem(can_receive_quantity {}, cost {}, created_by {}, created_date {}, quantity {}, status {}, vendor_sku {}, sku {}, purchase_order_id {}",
					new Object[]{simplePoItem.getCanReceiveQuantity(), simplePoItem.getCost(),  "System", "now", simplePoItem.getQuantity(), "Y", simplePoItem.getVenderSku(), simplePoItem.getSku(), simplePoItem.getPurchaseOrderId()});
		}
		
		//targetConnUtils.executeBatch(INSERT_POITEM, params);
		logger.info("init purchaseorder complete suncessNum : " + sucessNum + " failedNum : " + failedNum);
		//recevingDate
		//recevingDate(soip);
		final String updateSql = "UPDATE purchasebatch pb INNER JOIN ( SELECT sum(pi.quantity) AS quantity, po.batch_id FROM purchaseorder po INNER JOIN purchaseorderitem pi ON po.id = pi.purchase_order_id GROUP BY po.id ) t ON pb.id = t.batch_id SET pb.success_num = t.quantity";
		targetConnUtils.execute(updateSql);
	}
	
	private void recevingDate(Map<String, SimplePoItem> soip) {
		logger.info("################init product receive data #####################");
		final String insertProductReceving = "insert into warehouses.productreceive(uu_id, accept_quantity, created_by, created_date, invoice_number, received_by, received_date, wms_code, purchase_order_id,	purchase_order_item_id)  " +
				"select  uuid(), poi.quantity, 'System', now(), '', 'System', now(), 'WHE', ?, poi.id from supplychains.purchaseorderitem poi where sku = ?";
		targetConnUtils.execute(CHECK_FOREIGN_KEY);
		
		for (String sku : soip.keySet()) {
			SimplePoItem simplePoItem = soip.get(sku);
			targetConnUtils.execute(insertProductReceving, simplePoItem.getPurchaseOrderId(), sku);
			logger.info("productreceive quantity {},  sku {}", new Object[]{simplePoItem.getPurchaseOrderId(), sku});
		}
		
//		final String insertProductUnitSql = "insert into warehouses.productunit (created_by, created_date, is_Processed, processed_date, picking_quantity, quantity, received_quantity, skid_tag_number, status, wms_code, sku, receive_uu_id) " +
//				"values('System', now(), 1, now(), 0, ?, ?, ?, 'Y', ?, ?, '' )";
//		
//		for (String sku : soip.keySet()) {
//			SimplePoItem simplePoItem = soip.get(sku);
//			if (simplePoItem.getSkidTagNumber() == null || simplePoItem.getSkidTagNumber().length() == 0 ) {
//				logger.info( "Insert product unit quantity {0}, receving_quantity {1}, skidTagNumber {2}, wms_code {3}, sku {4}  error: skidTagNumber is null", new Object[]{simplePoItem.getQuantity(), simplePoItem.getQuantity(), simplePoItem.getSkidTagNumber(), "WHE", simplePoItem.getSku()});
//			} else {
//				targetConnUtils.execute(insertProductUnitSql, simplePoItem.getQuantity(), simplePoItem.getQuantity(), simplePoItem.getSkidTagNumber(), "WHE", simplePoItem.getSku());
//				logger.info( "Insert product unit quantity {0}, receving_quantity {1}, skidTagNumber {2}, wms_code {3}, sku {4} ", new Object[]{simplePoItem.getQuantity(), simplePoItem.getQuantity(), simplePoItem.getSkidTagNumber(), "WHE", simplePoItem.getSku()});
//			}
//		}
//		
//		logger.info( "Receving complete.");
		
		targetConnUtils.closeConnection();
	}
	
	public void dataTransfer () throws Exception {
		List<Object[]> poItems = new ArrayList<Object[]>();
		List<Object[]> excelDatas = getDateFormExcel();
		for (Object[] param : excelDatas) {
			Object[] poItem = new Object[]{param[4], param[5], param[4], "VENDOR#HS", "Y",  param[3]};
			poItems.add(poItem);
			logger.info("Insert Purchase Order Item can_receive_quantity {0}, cost {1}, quantity {2}, vendor_sku {3}, sku {4},  .", poItem);
		}
		try {
			targetConnUtils.execute(INSERT_VENDOR);
			targetConnUtils.execute(INSERT_POBATCH);
			targetConnUtils.execute(INSERTPO);
			targetConnUtils.executeBatch(INSERT_POITEM, poItems);
			logger.info("insert purchase order data complete");
		} catch (Exception e) {
			logger.info(e.getMessage());
		} finally {
			targetConnUtils.closeConnection();
		}
	}
	
	
	// 2 receving
	public void receving() throws Exception {
		final String insertProductReceving = "insert into warehouses.productreceive(uu_id, accept_quantity, created_by, created_date, invoice_number, received_by, received_date, wms_code, purchase_order_id,	purchase_order_item_id)  " +
				"select  uuid(), ?, 'System', now(), '', 'System', now(), ?, 1, poi.id from supplychains.purchaseorderitem poi where sku = ?";
		targetConnUtils.execute(CHECK_FOREIGN_KEY);
		List<Object[]> productRecevings = new ArrayList<Object[]>();
		List<Object[]> excelDatas = getDateFormExcel();
		for (Object[] param : excelDatas) {
			Object[] productReceving = new Object[]{param[4], param[0], param[3]};
			productRecevings.add(productReceving);
			logger.info( "Insert productreceive quantity {0}, wms_code {1}, sku {2} ", productReceving);
		}
		
		final String insertProductUnitSql = "insert into warehouses.productunit (created_by, created_date,is_Processed, processed_date, picking_quantity, quantity, received_quantity, skid_tag_number, status, wms_code, container_id, sku, receive_uu_id) " +
				"values('System', now(), 1, now(), 0, ?, ?, ?, 'Y', ?, ?, ?, '' )";
		List<Object[]> productUnits = new ArrayList<Object[]>();
		for (Object[] param : excelDatas) {
			Object[] productUnit = new Object[]{param[4], param[4], param[2], param[0], param[1], param[3]};
			productUnits.add(productUnit);
			logger.info( "Insert product unit quantity {0}, receving_quantity {1}, skidTagNumber {2}, wms_code {3}, bin_id {4}, sku {5} ", productUnit);
		}
		try {
			targetConnUtils.executeBatch(insertProductReceving, productRecevings);
			targetConnUtils.executeBatch(insertProductUnitSql, productUnits);
			logger.info( "Receving complete.");
		} catch (Exception e) {
			logger.info( e.getMessage());
		}finally {
			targetConnUtils.closeConnection();
		}
	}
	
	// 3 put away
	public void putAway() throws Exception {
		final String insertinventorySql = "insert into warehouses.inventory(available_quantity, created_by, created_date, onhand_quantity, " +
				"ordered_quantity, prepare_inventory_quantity, prepare_ordered_quantity, shipped_quantity, wms_code, sku)  " +
				"values(?, 'System', now(), ?, 0, 0, 0, 0, ?, ?)";
		List<Object[]> inventorys = new ArrayList<Object[]>();
		List<Object[]> excelDatas = getDateFormExcel();
		for (Object[] param : excelDatas) {
			Object[] inventory = new Object[]{param[4], param[4], param[0], param[3]};
			inventorys.add(inventory);
			logger.info( "Insert inventory quantity {0}, onhand_quantity {1}, wms_code {2}, sku {3} ", inventory);
		}
		
		final String insertVirtualInventorySql = "insert into products.virtualinventory(available_quantity,onhand_quantity,ordered_quantity,prepared_inventory_quantity," +
				"prepared_orderd_quantity,wms_code,product_item_rack_id) " +
				"select ?, ?, 0, 0, 0, ?, id from products.productitemrack where sku = ?";
		List<Object[]> virtualInventorys = new ArrayList<Object[]>();
		for (Object[] param : excelDatas) {
			Object[] virtualInventory = new Object[]{param[4], param[4], param[0], param[3]};
			virtualInventorys.add(virtualInventory);
			logger.info( "Insert inventory quantity {0}, onhand_quantity {1}, wms_code {2}, sku {3} ", virtualInventory);
		}
		
		try {
			targetConnUtils.execute(CHECK_FOREIGN_KEY);
			targetConnUtils.executeBatch(insertinventorySql, inventorys);
			targetConnUtils.executeBatch(insertVirtualInventorySql, virtualInventorys);
			logger.info( "Put away complete");
		} catch (Exception e) {
			logger.info(e.getMessage());
		} finally {
			targetConnUtils.closeConnection();
		}
		
	}
	
	// 4 save history
	public void saveHistory() {
		try {
			targetConnUtils.execute(II);
			targetConnUtils.execute(V_INVENTORY);
			targetConnUtils.execute(CHECK_FOREIGN_KEY);
			targetConnUtils.execute(INSERT_INVENTORY_DETAIL);
			targetConnUtils.execute(INVENTORY_CHANGE_HISTORY);
			logger.info( "Save change history complete");
		} catch (Exception e) {
			logger.info(e.getMessage());
		} finally {
			targetConnUtils.closeConnection();
		}
	}
	
	
	public List<Object[]> getDateFormExcel() throws Exception {
		InputStream inputStream = new FileInputStream("d:/po_inventory.xlsx");
        XSSFRow row = null;
        XSSFCell cell = null;
        XSSFWorkbook workBook = new XSSFWorkbook(inputStream);
        XSSFSheet sheet = workBook.getSheet("Sheet1");
        if (sheet == null) {
        	logger.info( "The excel is blank");
        }
        List<Object[]> excelDatas = new ArrayList<Object[]>();
        int rows = sheet.getPhysicalNumberOfRows();
        for (Integer k = 2, begin = 2; k < rows; begin++) {
        	Object[] excelData = new Object[6];
            row = sheet.getRow(begin);
            if (null != row) {
                k++;
                for (int j = 0; j < 6; j++) {
                    cell = row.getCell(j);
                    if (null != cell) {
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        excelData[j] = cell.getStringCellValue();
                    }
	            }
                excelDatas.add(excelData);
	        }
	    }
        inputStream.close();
        return excelDatas;
    }
	
	private void initconn () {
		Properties properties = new Properties();
		try {
			properties.load(Thread.currentThread().getContextClassLoader().getResourceAsStream("transaferjdbc.properties"));
			targetConnUtils = new ConnectionUtils(properties.getProperty("jdbc.target.user"), properties.getProperty("jdbc.target.password"), properties.getProperty("jdbc.target.url"));
			sourceConnUtils = new ConnectionUtils(properties.getProperty("jdbc.source.user"), properties.getProperty("jdbc.source.password"), properties.getProperty("jdbc.source.url"));
		} catch (IOException e) {
			logger.error(e.getMessage());
		}
	}
}
